package com.database;

import java.sql.*;
import java.util.*;



public class ServerSmsDataBase {
	private DataBaseConnection dataBaseConnection = null;

	private Connection connection = null;

	private Statement statement = null;

	private String customerName = null;

	private String pinNumber = null;

	private ResultSet resultSet = null;

	private TreeMap<String, String> accountDetails = null;

	private PreparedStatement preparedStatement = null;

	public ServerSmsDataBase() {
		try {
			dataBaseConnection = new DataBaseConnection();
			connection = dataBaseConnection.CallConnection();
			statement = connection.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public String SmsSubscribeDb(String accountNum) {
		Random random=new Random();
		String pinNumber="",accountNumber="";
		try {
			resultSet = statement
					.executeQuery("select * from ACCOUNT where ACCOUNTNUMBER='"
							+ accountNum + "'");
			while (resultSet.next()) {
				accountNumber=resultSet.getString("ACCOUNTNUMBER");
				}
			if (!accountNumber.equals("")) 
			{
				pinNumber=String.valueOf(random.nextInt(10))+String.valueOf(random.nextInt(10))+String.valueOf(random.nextInt(10))+String.valueOf(random.nextInt(10))+String.valueOf(random.nextInt(10));
				statement.executeUpdate("update ACCOUNT set PINNUMBER='"+pinNumber+"' where ACCOUNTNUMBER='"+accountNumber+"'");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pinNumber;
	}

	public String SmsUnsubscribeDb(String pinNum) {
		int status = 0;
		String resultUnsub = "";
		try {
			status = statement.executeUpdate("update ACCOUNT set PINNUMBER='' where PINNUMBER='"+pinNum+"'");
			if (status == 1) {
				resultUnsub = "Deactivated from the Service";
			}
			} catch (Exception e) {
			e.printStackTrace();
		}

		return resultUnsub;
	}

	public String SmsLastTransaction(String pinNum) {
		String lastAmount = null;
		//String lastDate = null;
		String result="";
		//TreeMap<String, String> transDetails = null;
		try {
			resultSet = statement
					.executeQuery("select * from ACCOUNT where PINNUMBER='"
							+ pinNum + "'");
			while (resultSet.next()) {
				//Timestamp timestamp = resultSet.getTimestamp("LASTTRANSACTION");
				//if (timestamp != null)
				//	lastDate = timestamp.toString();
				lastAmount = resultSet.getString("LASTAMT");
				if(lastAmount==null)
				{
					result="No Transaction list";
				}
				else
				{
					result="Last Transaction Action "+lastAmount;	
				}
				
				//transDetails.put("lastDate", lastDate);
				//transDetails.put("lastAmount", lastAmount);

			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		System.out.println("last..."+result);
		return result;
	}

	public String SmsIssuedCheque(String pinNum, String chequeNum) {
		String issuedChequeAmt = "";
		try {
			resultSet = statement
					.executeQuery("select * from ACCOUNT where PINNUMBER='"
							+ pinNum + "' and CHEQUENUM='" + chequeNum + "' ");
			while (resultSet.next()) {
				String issu = resultSet.getString("ISSUEDCHEQUEAMT");
				if(issu==null)
				{
					issuedChequeAmt="cheque Doesnt exist";
				}
				else
				{
					issuedChequeAmt="Issued cheque Amount is "+issu;
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return issuedChequeAmt;
	}

	public String SmsDepositedCheque(String pinNum, String chequeNum) {
		String depositedChequeAmt = "";
		try {
			resultSet = statement
					.executeQuery("select * from ACCOUNT where PINNUMBER='"
							+ pinNum + "' and CHEQUENUM='" + chequeNum + "' ");
			while (resultSet.next()) {
				String depo = resultSet.getString("DEPOSITEDCHEQUEAMT");
				if(depo==null)
				{
					depositedChequeAmt="cheque Doesnt exist";
				}
				else
				{
					depositedChequeAmt="Deposited cheque Amount is "+depo;
				}
			}
		
		} catch (Exception e) {
			e.printStackTrace();
		}
		return depositedChequeAmt;
	}

	public String SmsChangePin(String oldPin, String newPin) {
		int status = 0;
		String result = "";
		try {
			status=statement.executeUpdate("update ACCOUNT set pinnumber='" + newPin
					+ "' where pinnumber='" + oldPin + "'");
			if(status==1)
			result=newPin;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return result;

	}
	public String SmsBalAval(String pinNumber)
	{
		String balAval="";
		try
		{
			
			resultSet = statement
			.executeQuery("select * from ACCOUNT where PINNUMBER='"+pinNumber+"'");
			while(resultSet.next())
			{
				balAval=resultSet.getString("BALANCE");
			}
			
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return balAval;
	}
}
